Editing manual statistic accounts using a spreadsheet

You can use a spreadsheet to edit manual statistic values instead of using the manual statistics web page user interface. The data download as an Excel file includes all of the data that is currently available in the manual statistics editor on the web.

Deleting manual statistics in the downloaded spreadsheet does not remove them from the system. In fact, if you have a large amount of data in the spreadsheet and to help improve performance, we recommend that you remove all the unchanged rows from the downloaded file before uploading.

Pay special attention to the following before using this feature:

  • DO NOT rename the file name or the worksheets in this file.

  • DO NOT add columns, change column names, or change sheet tab names.

  • Ensure that there are no duplicate records.

  • General formatting is applied to all numbers downloaded in the spreadsheet that you will need to correct before uploading. For instructions, see the Spreadsheet formatting section below.

  • All required columns must be completed for each record before uploading.

  • Use caution when entering data into the spreadsheet. Neither the spreadsheet nor the system validates the data upon upload.

  • Upon upload back to the database, Axiom will remove department manual statistics from the CGL if the period columns include all zeroes.

  • Deleting records in the spreadsheet does not remove them from the system.

    TIP: If you have a large amount of data in the spreadsheet and to help improve performance, we recommend that you remove all the unchanged rows from the downloaded file before uploading.

  • If the spreadsheet includes a lot of data, it may take several minutes for the upload to complete before the system displays a confirmation message.

To edit manual statistic accounts using a spreadsheet:

  1. Add or edit a manual statistic account.
  2. In the upper right corner of the page, click one of the following:
    • Download table - Use this option to add or edit existing values for a manual statistic account.
    • Download template - Use this option if you are adding a new manual statistic account with no data (displays zero in columns for all departments). You can use this as a way to enter all values for a new account.

  3. Open the spreadsheet, or save the spreadsheet to a location first and then open it.

  4. Add a department by adding a row, or edit the column information for an existing department. If adding new rows, review the Spreadsheet formatting section below.

    IMPORTANT: To add a department, it must already exist in the core department dimension for the system.

  5. After making your changes, save the spreadsheet.

  6. In the manual statistic editor page , click Upload table.

  7. At the confirmation prompt, click Upload.

The table displays the row(s) where changes occurred and/or new rows added.

Spreadsheet formatting

When downloading the manual statistic values, they may display in the spreadsheet with General formatting. This is indicated by the green tick mark in the left corner in some cells—specifically number-based cells.

Large numbers

If you add new dimension rows that include large numbers, reformat them so they properly add to the database on return to the system by changing the number formatting to Number and the Decimal places field to zero.

Leading zeroes

For numbers that include leading zeroes, change the formatting to Text.

You can also simply add a single quote in front of the zero (this quote mark is not included in the data when it is uploaded).

Formulas

You can include formulas in the spreadsheet, and the system will only import the results into the database.